import openpyxl
from parameter_function import *
from tqdm import tqdm

wb = openpyxl.load_workbook(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\物控报表2024.08.02.xlsx")

for i in range(50, 0, -1):
    print(i)
    today = datetime.date.today()
    y1 = today - datetime.timedelta(days=i)
    y2 = y1.strftime("%Y.%m.%d")
    try:
        wb_y = openpyxl.load_workbook(
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + y2 + "/物控报表" + y2 + ".xlsx")
    except FileNotFoundError:
        continue
    temp = False
    temp2 = i
    while not temp:
        temp2 -= 1
        if temp2 < 0:
            break
        print(temp2)
        x1 = today - datetime.timedelta(days=temp2)
        x2 = x1.strftime("%Y.%m.%d")
        try:
            wb = openpyxl.load_workbook(
                "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + x2 + "/物控报表" + x2 + ".xlsx")
            temp = True
        except FileNotFoundError:
            pass
    print("//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + y2 + "/物控报表" + y2 + ".xlsx",
          "\n//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + x2 + "/物控报表" + x2 + ".xlsx")
    if temp2 < 0:
        break
    配件类物料90天库龄新增(wb, wb_y, x1)


def 配件类物料90天库龄新增(wb: openpyxl.workbook.workbook.Workbook, wb_y,x1):
    ws = wb.worksheets[0]

    # wb_y = openpyxl.load_workbook(
    #     "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + tools_pmc.yesterday_str1 + "/物控报表" + tools_pmc.yesterday_str1 + ".xlsx")
    ws_y = wb_y.worksheets[0]

    wb_异常 = openpyxl.load_workbook(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\异常监控台账.xlsx")
    ws_配件90天库存新增 = wb_异常["配件90天库存新增"]

    dict_col_y = dict_colf(wb_y)
    dict_col = dict_colf(wb)
    dict_xlandpj = {}
    for i in range(2, ws_y.max_row + 1):
        if (ws_y.cell(i, dict_col_y["类别"]).value == "相机配件"
                or ws_y.cell(i, dict_col_y["类别"]).value == "镜头"
                or ws_y.cell(i, dict_col_y["类别"]).value == "光源"
                or ws_y.cell(i, dict_col_y["类别"]).value == "线缆"):
            dict_xlandpj[ws_y.cell(i, dict_col_y["品号"]).value] = ws_y.cell(i, dict_col_y["在库超90天"]).value

    a = ws_配件90天库存新增.max_row + 1
    for i in range(2, ws.max_row + 1):
        if ws.cell(i, dict_col["品号"]).value in dict_xlandpj.keys() \
                and ws.cell(i, dict_col["在库超90天"]).value > dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value]\
                and ws.cell(i, dict_col["在库超90天"]).value - dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value]>5:
            # datetime.date.today(),
            list1 = [
                     x1,
                     ws.cell(i, dict_col["品号"]).value,
                     ws.cell(i, dict_col["品名"]).value,
                     ws.cell(i, dict_col["规格"]).value,
                     dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value],
                     ws.cell(i, dict_col["在库超90天"]).value,
                     ws.cell(i, dict_col["在库超90天"]).value - dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value]
                     ]
            for j in range(len(list1)):
                ws_配件90天库存新增.cell(a, j + 1).value = list1[j]
            ws_配件90天库存新增.cell(a, 1).number_format = 'yyyy/mm/dd'
            a += 1

    wb_异常.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\异常监控台账.xlsx")